package com.liusy.serachengine.parserFile;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ParserExcel {
	private static Logger log=Logger.getLogger(ParserExcel.class);
	 public static StringBuffer readXls(String path)  {
		 
	      StringBuffer content = new StringBuffer("");
	      File f = new File(path);
	      if(!f.exists())return content;
	      try {
	 		 if(path.substring(path.lastIndexOf(".")+ 1, path.length()).toLowerCase().equals("xlsx"))
				 return extractTextFromXLS2007(path);
	      FileInputStream fis = new FileInputStream(path);
	      HSSFWorkbook workbook = new HSSFWorkbook(fis);
	      int a = workbook.getNumberOfSheets();
	      for (int i = 0; i < a; i++) {
	         HSSFSheet sheet = workbook.getSheetAt(i);
	         if(null==sheet)continue;
	         int rows = sheet.getPhysicalNumberOfRows();
	         for (int r = 0; r < rows; r++) {
	            HSSFRow row = sheet.getRow(r);
	            if(null==row)continue;
	            int cells = row.getLastCellNum();
	            for (int c = 0; c < cells; c++) {
	               HSSFCell cell = row.getCell(c);
	               if (cell != null) {
	                  switch (cell.getCellType()) {
	                     case HSSFCell.CELL_TYPE_FORMULA:
	                        break;
	                     case HSSFCell.CELL_TYPE_NUMERIC:
	                    	 try{
	                    	 if((cell.getNumericCellValue()+"").indexOf(".")==-1)
	                    	 {DecimalFormat df = new DecimalFormat("#,##0.00");
	                        content.append(df.format(cell.getNumericCellValue()) + "  ");
	                        }
	                    	 else{
	                    		 DecimalFormat df = new DecimalFormat("#######################");
	 	                        content.append(df.format(cell.getNumericCellValue()) + "  ");
	                    	 }}catch(Exception e ){
	                    		   content.append(cell.getStringCellValue().trim()+ "  ");
	                    	 }
	                        break;
	                     case HSSFCell.CELL_TYPE_STRING:
	                        content.append(cell.getStringCellValue().trim() + "  ");
	                        break;
	                  }
	               }
	            }
	         }
	         fis.close();
	      }			
			} catch (Exception e) {
				log.error("Parser Excel errer:"+path, e);
				System.out.println("��ȡ�ļ�"+path+"���ı����ݳ��?����������δ���ɹ���");
			}
	      return content;
	   }
	 private static StringBuffer extractTextFromXLS2007(String fileName) throws Exception{    
		 StringBuffer content = new StringBuffer();    
	        
		     //���� XSSFWorkbook ����strPath �����ļ�·��        
		  XSSFWorkbook xwb = new XSSFWorkbook(fileName);    
	   
	  //ѭ��������Sheet    
	for(int numSheet = 0; numSheet < xwb.getNumberOfSheets(); numSheet++){    
	  XSSFSheet xSheet = xwb.getSheetAt(numSheet);     
	  if(xSheet == null){    
	  continue;    
	  }    
		     
		//ѭ����Row    
		for(int rowNum = 0; rowNum <= xSheet.getLastRowNum(); rowNum++){    
		  XSSFRow xRow = xSheet.getRow(rowNum);    
		   if(xRow == null){    
		   continue;    
	  }    
		      
	  //ѭ����Cell    
		   for(int cellNum = 0; cellNum <= xRow.getLastCellNum(); cellNum++){    
		    XSSFCell xCell = xRow.getCell(cellNum);    
		    if(xCell == null){    
		    continue;    
		    }    
		     if(xCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN){    
		      content.append(xCell.getBooleanCellValue());    
		      }else if(xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){   
		    	  try{
		         	 if((xCell.getNumericCellValue()+"").indexOf(".")==-1)
                	 {DecimalFormat df = new DecimalFormat("#,##0.00");
                    content.append(df.format(xCell.getNumericCellValue()) + "  ");
                    }
                	 else{
                		 DecimalFormat df = new DecimalFormat("#######################");
	                        content.append(df.format(xCell.getNumericCellValue()) + "  ");
                	 }
		      }catch(Exception e ){
       		   content.append(xCell.getStringCellValue()+ "  ");
       	 }
		      }else if(xCell.getCellType() == XSSFCell.CELL_TYPE_STRING){    
		     content.append(xCell.getStringCellValue());    
		     }    
		   }    
		   }    
		   }    
		       
		   return content;    
		    }   
 

}
